# _*_ coding:utf-8 _*_
# @Time  : 2022.10.11
# @Author: zizlee
import pandas as pd
from fastapi import APIRouter, Body, Query
from db import FAConnection
from v1_all_api.all_response import AllResponse
from v1_all_api.all_utils import datetime_utils


delivery_api = APIRouter()


@delivery_api.post('/')  # 保存交割数据
def save_delivery_data(delivery_data: list = Body(...)):
    if len(delivery_data) < 1:
        return AllResponse.operate_successfully(msg='保存成功，没有发现数据！')
    first_data = delivery_data[0]
    db_conn = FAConnection()
    # 查询数据是否存在，存在则今日已经保存过，不再保存
    exist_sql = "SELECT id FROM dat_futures_month_delivery WHERE delivery_date=%s AND variety_en=%s LIMIT 1;"
    exist = db_conn.query(exist_sql, param=[first_data['delivery_date'], first_data['variety_en']], fetchone=True,
                          keep_conn=True)[0]
    if exist:
        db_conn.close()
        return AllResponse.forbidden_error(msg='今日数据该交易所数据已保存，请勿重复保存！')
    # 把偶才能数据
    save_sql = """
        INSERT IGNORE INTO dat_futures_month_delivery (delivery_date,variety_en,volume,amount,volume_unit,amount_unit) 
        VALUES (%(delivery_date)s,%(variety_en)s,%(volume)s,%(amount)s,%(volume_unit)s,%(amount_unit)s);
    """
    count, _ = db_conn.insert(save_sql, param=delivery_data, many=True)
    return AllResponse.operate_successfully(msg=f'保存入库数量{count}！')


@delivery_api.get('/')  # 查询交割数据
def query_delivery_data(start: str = Query(...), end: str = Query(...), v: str = Query(None)):
    start_date = datetime_utils.auth_datetime_string(start, f='%Y%m%d')
    end_date = datetime_utils.auth_datetime_string(end, f='%Y%m%d')
    if not all([start_date, end_date]):
        return AllResponse.validate_error(msg='invalid start and end query-string.')
    variety_en = '0' if not v else v
    sql = """
        SELECT id,delivery_date,variety_en,volume,amount,volume_unit,amount_unit FROM dat_futures_month_delivery 
        WHERE delivery_date>=%s AND delivery_date<=%s AND IF('0'=%s,TRUE,variety_en=%s) ORDER BY delivery_date;
    """
    prm = [start_date, end_date, variety_en, variety_en]
    db_conn = FAConnection()
    records = db_conn.query(sql, param=prm)
    return AllResponse.operate_successfully(data=records)
